﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DTO;

namespace DAO
{
    public class sqlConnnection
    {
        public static SqlConnection KetNoi()
        {
            SqlConnection cnn = new SqlConnection(@"Data Source=TRANTHANH-PC\SQLEXPRESS;Initial Catalog=BanSach;Integrated Security=True");
            return cnn;
        }
    }
    public class NXB_DAO
    {
        //load danh sach NXB:
        public static DataTable LoadDSNXB()
        {
            SqlConnection cnn = sqlConnnection.KetNoi();
            SqlCommand cmd = new SqlCommand("SelectAll_NXB", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        //Them NXB Moi:
        public static void ThemNXB(NXB_DTO nxb)
        {
            SqlConnection cnn = sqlConnnection.KetNoi();
            SqlCommand cmd = new SqlCommand("insert_NXB", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@maNXB", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@tenNXB", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@SDT", SqlDbType.Char, 15);
            cmd.Parameters.Add("@Fax", SqlDbType.Char, 15);
            cmd.Parameters.Add("@MaTK", SqlDbType.Char, 15);
            //gan:
            cmd.Parameters["@maNXB"].Value = nxb.MaNXB;
            cmd.Parameters["@tenNXB"].Value = nxb.TenNXB;
            cmd.Parameters["@DiaChi"].Value = nxb.DiaChi;
            cmd.Parameters["@SDT"].Value = nxb.SDT;
            cmd.Parameters["@Fax"].Value = nxb.Fax;
            cmd.Parameters["@MaTK"].Value = nxb.MaTK;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        //Sua NXB:
        public static void SuaNXB(NXB_DTO sua)
        {
            SqlConnection cnn = sqlConnnection.KetNoi();
            SqlCommand cmd = new SqlCommand("Update_NXB", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@maNXB", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@tenNXB", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@SDT", SqlDbType.Char, 15);
            cmd.Parameters.Add("@Fax", SqlDbType.Char, 15);
            cmd.Parameters.Add("@MaTK", SqlDbType.Char, 15);
            //gan:
            cmd.Parameters["@maNXB"].Value = sua.MaNXB;
            cmd.Parameters["@tenNXB"].Value = sua.TenNXB;
            cmd.Parameters["@DiaChi"].Value = sua.DiaChi;
            cmd.Parameters["@SDT"].Value = sua.SDT;
            cmd.Parameters["@Fax"].Value = sua.Fax;
            cmd.Parameters["@MaTK"].Value = sua.MaTK;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        //XoaNXB:
        public static void XoaNXB(string manxb)
        {
            SqlConnection cnn = sqlConnnection.KetNoi();
            SqlCommand cmd = new SqlCommand("[Delete_NXB]", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@maNXB", SqlDbType.NChar, 10);
            cmd.Parameters["@maNXB"].Value =manxb;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        //TimTenNXB:
        public static DataTable TimTenNXB(string ten)
        {
            SqlConnection cnn = sqlConnnection.KetNoi();
            SqlCommand cmd = new SqlCommand("Search_NXB", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@code", SqlDbType.NVarChar, 50);
            cmd.Parameters["@code"].Value = ten;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
    }
}
